
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure
-- --------------------------------------------------
-- Date Created: 10/26/2014 14:33:59
-- Generated from EDMX file: D:\desarrollo\AppGISENA\AppGisena\Models\Model1GisenaBD.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [BDGISENA];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Caracterizacion_UP_Estado_UP]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Caracterizacion_UP] DROP CONSTRAINT [FK_Caracterizacion_UP_Estado_UP];
GO
IF OBJECT_ID(N'[dbo].[FK_Caracterizacion_UP_Tp_UP]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Caracterizacion_UP] DROP CONSTRAINT [FK_Caracterizacion_UP_Tp_UP];
GO
IF OBJECT_ID(N'[dbo].[FK_Caracterizacion_UP_UP_Sofia]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Caracterizacion_UP] DROP CONSTRAINT [FK_Caracterizacion_UP_UP_Sofia];
GO
IF OBJECT_ID(N'[dbo].[FK_Productos_Ofertados_Caracterizacion_UP]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Productos_Ofertados] DROP CONSTRAINT [FK_Productos_Ofertados_Caracterizacion_UP];
GO
IF OBJECT_ID(N'[dbo].[FK_Productos_Ofertados_Productos]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Productos_Ofertados] DROP CONSTRAINT [FK_Productos_Ofertados_Productos];
GO
IF OBJECT_ID(N'[dbo].[FK_UP_Campo_Caracterizacion_UP]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UP_Campo] DROP CONSTRAINT [FK_UP_Campo_Caracterizacion_UP];
GO
IF OBJECT_ID(N'[dbo].[FK_Vinc_Aprendiz_Caracterizacion_UP]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Vincular_Socio] DROP CONSTRAINT [FK_Vinc_Aprendiz_Caracterizacion_UP];
GO
IF OBJECT_ID(N'[dbo].[FK_Vinc_Aprendiz_Rol_Aprendiz]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Vincular_Socio] DROP CONSTRAINT [FK_Vinc_Aprendiz_Rol_Aprendiz];
GO
IF OBJECT_ID(N'[dbo].[FK_Vincular_Asesor_Caracterizacion_UP]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Vincular_Asesor] DROP CONSTRAINT [FK_Vincular_Asesor_Caracterizacion_UP];
GO
IF OBJECT_ID(N'[dbo].[FK_Vincular_Socio_Socio]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Vincular_Socio] DROP CONSTRAINT [FK_Vincular_Socio_Socio];
GO
IF OBJECT_ID(N'[dbo].[FK_Vinculo_Instructor_Instructor]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Vincular_Asesor] DROP CONSTRAINT [FK_Vinculo_Instructor_Instructor];
GO
IF OBJECT_ID(N'[dbo].[FK_Vinculo_Instructor_Tp_Perfil]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Vincular_Asesor] DROP CONSTRAINT [FK_Vinculo_Instructor_Tp_Perfil];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Asesor]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Asesor];
GO
IF OBJECT_ID(N'[dbo].[Caracterizacion_UP]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Caracterizacion_UP];
GO
IF OBJECT_ID(N'[dbo].[Estado_UP]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Estado_UP];
GO
IF OBJECT_ID(N'[dbo].[Productos]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Productos];
GO
IF OBJECT_ID(N'[dbo].[Productos_Ofertados]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Productos_Ofertados];
GO
IF OBJECT_ID(N'[dbo].[Rol_Socio]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Rol_Socio];
GO
IF OBJECT_ID(N'[dbo].[Socio]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Socio];
GO
IF OBJECT_ID(N'[dbo].[Tp_Perfil_Asesor]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tp_Perfil_Asesor];
GO
IF OBJECT_ID(N'[dbo].[Tp_UP]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tp_UP];
GO
IF OBJECT_ID(N'[dbo].[UP_Campo]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UP_Campo];
GO
IF OBJECT_ID(N'[dbo].[UP_Sofia]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UP_Sofia];
GO
IF OBJECT_ID(N'[dbo].[Vincular_Asesor]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Vincular_Asesor];
GO
IF OBJECT_ID(N'[dbo].[Vincular_Socio]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Vincular_Socio];
GO
IF OBJECT_ID(N'[BDGISENAModelStoreContainer].[VistaR1]', 'U') IS NOT NULL
    DROP TABLE [BDGISENAModelStoreContainer].[VistaR1];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Caracterizacion_UP'
CREATE TABLE [dbo].[Caracterizacion_UP] (
    [Cod_UP] bigint  NOT NULL,
    [Fech_Reg] datetime  NULL,
    [Descripcion_UP] varchar(300)  NULL,
    [Id_Est_up] int  NULL,
    [Num_Act_Mat] varchar(20)  NULL,
    [Id_Tp_UP] int  NULL
);
GO

-- Creating table 'Estado_UP'
CREATE TABLE [dbo].[Estado_UP] (
    [Id_Est] int IDENTITY(1,1) NOT NULL,
    [Nombre] varchar(20)  NULL
);
GO

-- Creating table 'Productos'
CREATE TABLE [dbo].[Productos] (
    [Id_Prod] bigint  NOT NULL,
    [Nomb_Prod] varchar(100)  NULL,
    [Descrip] varchar(100)  NULL,
    [Id_Cate] int  NULL,
    [Id_Tp_Prod] bigint  NULL
);
GO

-- Creating table 'Productos_Ofertados'
CREATE TABLE [dbo].[Productos_Ofertados] (
    [Principal] bit  NULL,
    [Cod_UP] bigint  NULL,
    [Id_Prod_Ofert] bigint IDENTITY(1,1) NOT NULL,
    [Id_Prod] bigint  NULL
);
GO

-- Creating table 'Tp_UP'
CREATE TABLE [dbo].[Tp_UP] (
    [Id_Tp_UP] int IDENTITY(1,1) NOT NULL,
    [Nombre] varchar(50)  NULL
);
GO

-- Creating table 'UP_Campo'
CREATE TABLE [dbo].[UP_Campo] (
    [Cod_UP] bigint  NOT NULL,
    [Dir_Predio] varchar(100)  NULL,
    [Tenecia] varchar(50)  NULL,
    [Area_Predio] decimal(18,0)  NULL,
    [Area_Potencial] decimal(18,0)  NULL,
    [Area_UP] decimal(18,0)  NULL,
    [Tipo_Predio] varchar(20)  NULL,
    [Vereda_Barrio] varchar(100)  NULL,
    [Altura_GNS] decimal(18,0)  NULL,
    [Shape] geography  NULL,
    [Nom_Predio] varchar(100)  NULL,
    [Foto] varbinary(max)  NULL
);
GO

-- Creating table 'UP_Sofia'
CREATE TABLE [dbo].[UP_Sofia] (
    [Cod_UP] bigint  NOT NULL,
    [Nomb_UP] varchar(100)  NULL,
    [NFicha_UP] varchar(20)  NULL,
    [Fecha_Creac] datetime  NULL,
    [Cod_Cent_Form] int  NULL,
    [Fecha_Ent_Mat] datetime  NULL
);
GO

-- Creating table 'VistaR1'
CREATE TABLE [dbo].[VistaR1] (
    [Cod_UP] bigint  NOT NULL,
    [Nomb_UP] varchar(100)  NULL,
    [Descripcion_UP] varchar(300)  NULL,
    [Tipo_UP] varchar(50)  NULL,
    [Vereda_Barrio] varchar(100)  NULL,
    [Area_UP] decimal(18,0)  NULL,
    [Fech_Reg] datetime  NULL,
    [Centro_Formacion] varchar(120)  NULL,
    [Shape] geography  NULL
);
GO

-- Creating table 'Asesor'
CREATE TABLE [dbo].[Asesor] (
    [Identificacion] bigint  NOT NULL,
    [Ficha_Caract] bigint  NULL,
    [Nombre] varchar(50)  NULL,
    [Apellido] varchar(50)  NULL,
    [Tp_Doc] varchar(20)  NULL,
    [Correo] varchar(50)  NULL,
    [Celular] varchar(20)  NULL,
    [Est_Cont] bit  NULL,
    [Direccion] varchar(50)  NULL,
    [Ciudad] varchar(50)  NULL
);
GO

-- Creating table 'Rol_Socio'
CREATE TABLE [dbo].[Rol_Socio] (
    [Id_Rol] int IDENTITY(1,1) NOT NULL,
    [Nombre] varchar(50)  NULL
);
GO

-- Creating table 'Socio'
CREATE TABLE [dbo].[Socio] (
    [Nombre] varchar(50)  NULL,
    [Apellidos] varchar(50)  NULL,
    [Tp_Doc] varchar(20)  NULL,
    [Identificacion] bigint  NOT NULL,
    [Estado] varchar(20)  NULL,
    [Ficha_Carct] bigint  NULL,
    [Correo] varchar(50)  NULL,
    [Fech_Nac] datetime  NULL,
    [Telefono] varchar(20)  NULL,
    [Direccion] varchar(50)  NULL,
    [Id_Tp_Pobl] int  NULL
);
GO

-- Creating table 'Tp_Perfil_Asesor'
CREATE TABLE [dbo].[Tp_Perfil_Asesor] (
    [Id_Tp_Perfil] int IDENTITY(1,1) NOT NULL,
    [Nombre] varchar(50)  NULL
);
GO

-- Creating table 'Vincular_Asesor'
CREATE TABLE [dbo].[Vincular_Asesor] (
    [Id_Int_Vinc] bigint IDENTITY(1,1) NOT NULL,
    [Ident_Instr] bigint  NULL,
    [Id_Tp_Perfil] int  NOT NULL,
    [Cod_UP] bigint  NULL
);
GO

-- Creating table 'Vincular_Socio'
CREATE TABLE [dbo].[Vincular_Socio] (
    [Descripcion] varchar(100)  NULL,
    [Estado] varchar(20)  NULL,
    [Id_Rol] int  NULL,
    [Ident_Soc] bigint  NOT NULL,
    [Cod_UP] bigint  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Cod_UP] in table 'Caracterizacion_UP'
ALTER TABLE [dbo].[Caracterizacion_UP]
ADD CONSTRAINT [PK_Caracterizacion_UP]
    PRIMARY KEY CLUSTERED ([Cod_UP] ASC);
GO

-- Creating primary key on [Id_Est] in table 'Estado_UP'
ALTER TABLE [dbo].[Estado_UP]
ADD CONSTRAINT [PK_Estado_UP]
    PRIMARY KEY CLUSTERED ([Id_Est] ASC);
GO

-- Creating primary key on [Id_Prod] in table 'Productos'
ALTER TABLE [dbo].[Productos]
ADD CONSTRAINT [PK_Productos]
    PRIMARY KEY CLUSTERED ([Id_Prod] ASC);
GO

-- Creating primary key on [Id_Prod_Ofert] in table 'Productos_Ofertados'
ALTER TABLE [dbo].[Productos_Ofertados]
ADD CONSTRAINT [PK_Productos_Ofertados]
    PRIMARY KEY CLUSTERED ([Id_Prod_Ofert] ASC);
GO

-- Creating primary key on [Id_Tp_UP] in table 'Tp_UP'
ALTER TABLE [dbo].[Tp_UP]
ADD CONSTRAINT [PK_Tp_UP]
    PRIMARY KEY CLUSTERED ([Id_Tp_UP] ASC);
GO

-- Creating primary key on [Cod_UP] in table 'UP_Campo'
ALTER TABLE [dbo].[UP_Campo]
ADD CONSTRAINT [PK_UP_Campo]
    PRIMARY KEY CLUSTERED ([Cod_UP] ASC);
GO

-- Creating primary key on [Cod_UP] in table 'UP_Sofia'
ALTER TABLE [dbo].[UP_Sofia]
ADD CONSTRAINT [PK_UP_Sofia]
    PRIMARY KEY CLUSTERED ([Cod_UP] ASC);
GO

-- Creating primary key on [Cod_UP] in table 'VistaR1'
ALTER TABLE [dbo].[VistaR1]
ADD CONSTRAINT [PK_VistaR1]
    PRIMARY KEY CLUSTERED ([Cod_UP] ASC);
GO

-- Creating primary key on [Identificacion] in table 'Asesor'
ALTER TABLE [dbo].[Asesor]
ADD CONSTRAINT [PK_Asesor]
    PRIMARY KEY CLUSTERED ([Identificacion] ASC);
GO

-- Creating primary key on [Id_Rol] in table 'Rol_Socio'
ALTER TABLE [dbo].[Rol_Socio]
ADD CONSTRAINT [PK_Rol_Socio]
    PRIMARY KEY CLUSTERED ([Id_Rol] ASC);
GO

-- Creating primary key on [Identificacion] in table 'Socio'
ALTER TABLE [dbo].[Socio]
ADD CONSTRAINT [PK_Socio]
    PRIMARY KEY CLUSTERED ([Identificacion] ASC);
GO

-- Creating primary key on [Id_Tp_Perfil] in table 'Tp_Perfil_Asesor'
ALTER TABLE [dbo].[Tp_Perfil_Asesor]
ADD CONSTRAINT [PK_Tp_Perfil_Asesor]
    PRIMARY KEY CLUSTERED ([Id_Tp_Perfil] ASC);
GO

-- Creating primary key on [Id_Int_Vinc] in table 'Vincular_Asesor'
ALTER TABLE [dbo].[Vincular_Asesor]
ADD CONSTRAINT [PK_Vincular_Asesor]
    PRIMARY KEY CLUSTERED ([Id_Int_Vinc] ASC);
GO

-- Creating primary key on [Ident_Soc], [Cod_UP] in table 'Vincular_Socio'
ALTER TABLE [dbo].[Vincular_Socio]
ADD CONSTRAINT [PK_Vincular_Socio]
    PRIMARY KEY CLUSTERED ([Ident_Soc], [Cod_UP] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Id_Est_up] in table 'Caracterizacion_UP'
ALTER TABLE [dbo].[Caracterizacion_UP]
ADD CONSTRAINT [FK_Caracterizacion_UP_Estado_UP]
    FOREIGN KEY ([Id_Est_up])
    REFERENCES [dbo].[Estado_UP]
        ([Id_Est])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Caracterizacion_UP_Estado_UP'
CREATE INDEX [IX_FK_Caracterizacion_UP_Estado_UP]
ON [dbo].[Caracterizacion_UP]
    ([Id_Est_up]);
GO

-- Creating foreign key on [Id_Tp_UP] in table 'Caracterizacion_UP'
ALTER TABLE [dbo].[Caracterizacion_UP]
ADD CONSTRAINT [FK_Caracterizacion_UP_Tp_UP]
    FOREIGN KEY ([Id_Tp_UP])
    REFERENCES [dbo].[Tp_UP]
        ([Id_Tp_UP])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Caracterizacion_UP_Tp_UP'
CREATE INDEX [IX_FK_Caracterizacion_UP_Tp_UP]
ON [dbo].[Caracterizacion_UP]
    ([Id_Tp_UP]);
GO

-- Creating foreign key on [Cod_UP] in table 'Caracterizacion_UP'
ALTER TABLE [dbo].[Caracterizacion_UP]
ADD CONSTRAINT [FK_Caracterizacion_UP_UP_Sofia]
    FOREIGN KEY ([Cod_UP])
    REFERENCES [dbo].[UP_Sofia]
        ([Cod_UP])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Cod_UP] in table 'Productos_Ofertados'
ALTER TABLE [dbo].[Productos_Ofertados]
ADD CONSTRAINT [FK_Productos_Ofertados_Caracterizacion_UP]
    FOREIGN KEY ([Cod_UP])
    REFERENCES [dbo].[Caracterizacion_UP]
        ([Cod_UP])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Productos_Ofertados_Caracterizacion_UP'
CREATE INDEX [IX_FK_Productos_Ofertados_Caracterizacion_UP]
ON [dbo].[Productos_Ofertados]
    ([Cod_UP]);
GO

-- Creating foreign key on [Cod_UP] in table 'UP_Campo'
ALTER TABLE [dbo].[UP_Campo]
ADD CONSTRAINT [FK_UP_Campo_Caracterizacion_UP]
    FOREIGN KEY ([Cod_UP])
    REFERENCES [dbo].[Caracterizacion_UP]
        ([Cod_UP])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id_Prod] in table 'Productos_Ofertados'
ALTER TABLE [dbo].[Productos_Ofertados]
ADD CONSTRAINT [FK_Productos_Ofertados_Productos]
    FOREIGN KEY ([Id_Prod])
    REFERENCES [dbo].[Productos]
        ([Id_Prod])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Productos_Ofertados_Productos'
CREATE INDEX [IX_FK_Productos_Ofertados_Productos]
ON [dbo].[Productos_Ofertados]
    ([Id_Prod]);
GO

-- Creating foreign key on [Ident_Instr] in table 'Vincular_Asesor'
ALTER TABLE [dbo].[Vincular_Asesor]
ADD CONSTRAINT [FK_Vinculo_Instructor_Instructor]
    FOREIGN KEY ([Ident_Instr])
    REFERENCES [dbo].[Asesor]
        ([Identificacion])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Vinculo_Instructor_Instructor'
CREATE INDEX [IX_FK_Vinculo_Instructor_Instructor]
ON [dbo].[Vincular_Asesor]
    ([Ident_Instr]);
GO

-- Creating foreign key on [Cod_UP] in table 'Vincular_Socio'
ALTER TABLE [dbo].[Vincular_Socio]
ADD CONSTRAINT [FK_Vinc_Aprendiz_Caracterizacion_UP]
    FOREIGN KEY ([Cod_UP])
    REFERENCES [dbo].[Caracterizacion_UP]
        ([Cod_UP])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Vinc_Aprendiz_Caracterizacion_UP'
CREATE INDEX [IX_FK_Vinc_Aprendiz_Caracterizacion_UP]
ON [dbo].[Vincular_Socio]
    ([Cod_UP]);
GO

-- Creating foreign key on [Cod_UP] in table 'Vincular_Asesor'
ALTER TABLE [dbo].[Vincular_Asesor]
ADD CONSTRAINT [FK_Vincular_Asesor_Caracterizacion_UP]
    FOREIGN KEY ([Cod_UP])
    REFERENCES [dbo].[Caracterizacion_UP]
        ([Cod_UP])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Vincular_Asesor_Caracterizacion_UP'
CREATE INDEX [IX_FK_Vincular_Asesor_Caracterizacion_UP]
ON [dbo].[Vincular_Asesor]
    ([Cod_UP]);
GO

-- Creating foreign key on [Id_Rol] in table 'Vincular_Socio'
ALTER TABLE [dbo].[Vincular_Socio]
ADD CONSTRAINT [FK_Vinc_Aprendiz_Rol_Aprendiz]
    FOREIGN KEY ([Id_Rol])
    REFERENCES [dbo].[Rol_Socio]
        ([Id_Rol])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Vinc_Aprendiz_Rol_Aprendiz'
CREATE INDEX [IX_FK_Vinc_Aprendiz_Rol_Aprendiz]
ON [dbo].[Vincular_Socio]
    ([Id_Rol]);
GO

-- Creating foreign key on [Ident_Soc] in table 'Vincular_Socio'
ALTER TABLE [dbo].[Vincular_Socio]
ADD CONSTRAINT [FK_Vincular_Socio_Socio]
    FOREIGN KEY ([Ident_Soc])
    REFERENCES [dbo].[Socio]
        ([Identificacion])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id_Tp_Perfil] in table 'Vincular_Asesor'
ALTER TABLE [dbo].[Vincular_Asesor]
ADD CONSTRAINT [FK_Vinculo_Instructor_Tp_Perfil]
    FOREIGN KEY ([Id_Tp_Perfil])
    REFERENCES [dbo].[Tp_Perfil_Asesor]
        ([Id_Tp_Perfil])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Vinculo_Instructor_Tp_Perfil'
CREATE INDEX [IX_FK_Vinculo_Instructor_Tp_Perfil]
ON [dbo].[Vincular_Asesor]
    ([Id_Tp_Perfil]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------